﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Garlic.DM.DataModel;

namespace DAL.SystemManagement
{
    public class FlightInfoDAL
    {
        public static DataTable GetShipAgency(int user_id)
        {
            string sqlstr = "select c.* from st_customers c inner join  ts_bas_user tbu on tbu.company =c.cst_id where tbu.user_id="+user_id;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
            return db.ExecuteDataTable(dbCommand);
        }

        public static DataTable GetFlt_schdulDataTable(int flt_sln_id)
        {
            string sqlstr = @"select 
                     flt.FLT_TER_CST_ID, flt.FLT_SCHDUL,flt.FLT_ID,
                    cstm.cst_cstmnm, cstm.cst_cstmcd, cstm.cst_cstm
                    from st_flights flt
                    left join st_customers cstm on flt.FLT_TER_CST_ID = cstm.cst_id
                     where flt.flt_sln_id = " + flt_sln_id;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
            return db.ExecuteDataTable(dbCommand);
        }

        public static DataTable GetFlt_schdulDataTable(int flt_sln_id,int flt_id)
        {
            string sqlstr = @"select 
                     flt.FLT_TER_CST_ID, flt.FLT_SCHDUL,flt.FLT_ID,
                    cstm.cst_cstmnm, cstm.cst_cstmcd, cstm.cst_cstm
                    from st_flights flt
                    left join st_customers cstm on flt.FLT_TER_CST_ID = cstm.cst_id
                     where flt.flt_sln_id = " + flt_sln_id + "  and flt.flt_id= " + flt_id;
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
            return db.ExecuteDataTable(dbCommand);
        }

        public static DataTable GetFlt_TerminalDataTable()
        {
            string sqlstr = "select c.cst_id,c.cst_cstm,c.cst_cstmcd,c.cst_cstmnm from st_customers c inner join st_customer_types ct on c.cst_id=ct.cmp_cst_id and ct.cmp_type='7' and ct.cmp_validfg='Y'";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
            return db.ExecuteDataTable(dbCommand);
        }

        /// <summary>
        /// 根据服务航线和代理公司获取班期和航班ID
        /// </summary>
        /// <param name="agentID"></param>
        /// <param name="flt_sln_id"></param>
        /// <returns></returns>
        public static DataTable GetFlightScheduleBySln(int? agentID, string flt_sln_id)
        {
            string sql  =string.Empty;
            if (agentID == null)
            {
                sql = @"select
            flt.FLT_ID,flt.FLT_NO,flt.FLT_SCHDUL,(flt.FLT_SCHDUL||'('||FLT_NO||')') ScheduleNo
                          from ST_FLIGHTS flt
                          left join ST_SERVICE_LINES sl on sl.SLN_ID = flt.FLT_SLN_ID
                           where    flt.FLT_SLN_ID  like '%{0}%'   order by ScheduleNo ";

                sql = string.Format(sql, flt_sln_id);
            }
            else
            {
                sql = @"select
                      flt.FLT_ID,flt.FLT_NO,flt.FLT_SCHDUL,(flt.FLT_SCHDUL||'('||FLT_NO||')') ScheduleNo
                          from ST_FLIGHTS flt
                          left join ST_SERVICE_LINES sl on sl.SLN_ID = flt.FLT_SLN_ID
                           where    flt.FLT_SLN_ID  like '%{1}%'  ";
                sql = string.Format((sql + "and  (sl.SLN_CST_VCM_ID ={0}  or sl.SLN_CST_AGT_ID= {0})  order by ScheduleNo"), agentID, flt_sln_id);

            }
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            return db.ExecuteDataTable(dbCommand);

        }

        /// <summary>
        /// 获取服务航线信息    
        /// </summary>
        /// <param name="agentID">代理商ID</param>
        /// <param name="flt_sln_id">服务航线ID</param>
        /// <param name="schedule">预分码头ID</param>
        /// <returns></returns>
        public static DataTable GetSt_flightsDataTable( int? agentID, string flt_sln_id, string teminalID)
        {

            #region old
//            string sqlstr = @"select flt.*,sln.sln_name
//                             from st_flights flt left join st_service_lines sln on sln.sln_id=flt.flt_sln_id
//                            left join st_customers cus on cus.cst_id=flt.flt_ter_cst_id where 1=1 ";
//            if (flt_sln_id != "")
//                sqlstr += "and flt.flt_sln_id = " + flt_sln_id;
//            if (flt_ter_cst_id != "")
//                sqlstr += String.Format(" and flt.flt_ter_cst_id ='{0}'", schedule);
           
            #endregion
            
            string sql = @"select sr.SRT_ID, sr.SRT_RTCHNNM,
                          sl.SLN_NAME,  sl.SLN_ID, sl.SLN_NAME SLN_NAME1,  sl.SLN_CST_VCM_ID, sl.SLN_CST_AGT_ID,sl.SLN_ID,sl.SLN_TER_CST_ID,
                          cst.CST_CSTMNM,cst.CST_CSTM,cst.cst_cstmcd,
                          flt.FLT_ID,flt.FLT_NO,flt.FLT_SCHDUL,flt.FLT_CYCLE,flt.FLT_INITDT,flt.FLT_IAMOUNT,flt.FLT_EAMOUNT,flt.FLT_DOCKHOUR
                          from ST_FLIGHTS flt
                          left join ST_SERVICE_LINES sl on sl.SLN_ID = flt.FLT_SLN_ID
                          left join ST_SEA_ROUTES sr on sr.SRT_ID = sl.sln_srt_id,
                          st_customers cst where cst.cst_id =flt.FLT_TER_CST_ID and  {2} flt.FLT_SLN_ID  like '%{1}%' and  flt.flt_ter_cst_id  like '%{0}%'
                          order by sr.SRT_RTCHNNM, sl.SLN_NAME";

            if (agentID == null)
            {
                sql = string.Format(sql, teminalID, flt_sln_id, "");

            }
            else
            {
                sql = string.Format(sql, teminalID, flt_sln_id, string.Format(" (sl.SLN_CST_VCM_ID ={0}  or sl.SLN_CST_AGT_ID= {0}) and ", agentID));
            }

        




            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
          //  db.AddInParameter(dbCommand, "AGTID", DbType.Int32, agentID);
            return db.ExecuteDataTable(dbCommand);
        }

        public static int Update_St_flights(int flt_id,  string flt_cycle,
            string flt_initdt, string flt_dockhour, string flt_iamount, string flt_eamount,  string user)
        {
            string sqlstr = @"update st_flights   
                              set 
                              
                                flt_cycle=:flt_cycle,
                                flt_initdt=:flt_initdt,
                                flt_dockhour=:flt_dockhour,
                                flt_iamount=:flt_iamount,
                                flt_eamount=:flt_eamount,
                                
                                FLT_LSTUPDDT=:FLT_LSTUPDDT,
                                FLT_LSTUPUSER=:FLT_LSTUPUSER
                              where 
                                flt_id = :flt_id";

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
            //db.AddInParameter(dbCommand, "flt_sln_id", DbType.Int32, flt_sln_id);
            //db.AddInParameter(dbCommand, "flt_no", DbType.String, flt_no);
          //  db.AddInParameter(dbCommand, "flt_schdul", DbType.String, flt_schdul);
            db.AddInParameter(dbCommand, "flt_cycle", DbType.Int32, flt_cycle);
            db.AddInParameter(dbCommand, "flt_initdt", DbType.Date, flt_initdt);
            ParameterAdd(flt_dockhour, "flt_dockhour", db, dbCommand);
            ParameterAdd(flt_iamount, "flt_iamount", db, dbCommand);
            ParameterAdd(flt_eamount, "flt_eamount", db, dbCommand);
            db.AddInParameter(dbCommand, "flt_id", DbType.Int32, flt_id);
          //  db.AddInParameter(dbCommand, "FLT_SLN_ID", DbType.Int32, Int32.Parse(sln));

            db.AddInParameter(dbCommand, "FLT_LSTUPDDT", DbType.Date, DateTime.Now);
            db.AddInParameter(dbCommand, "FLT_LSTUPUSER", DbType.String, user);

            return db.ExecuteNonQuery(dbCommand);
        }

        public static int Insert_St_flights( string flt_sln_id, string FLT_TER_CST_ID, string flt_schdul, string flt_cycle, 
            string flt_initdt, string flt_dockhour, string flt_iamount, string flt_eamount, string user)
        {
            string orsql = @"insert into st_flights (FLT_FIXSCDSHP,flt_id,FLT_TER_CST_ID, flt_sln_id, flt_no, 
                                flt_schdul, flt_cycle,
                               flt_initdt, flt_dockhour, flt_iamount, flt_eamount, FLT_INSERTUSER,FLT_INSERTDT)
                              select 'Y',  bps_flt_seq1.nextval ,t.* from  (select :FLT_TER_CST_ID, :flt_sln_id, nvl( max(FLT_NO), 0)+1, 
                                :flt_schdul, :flt_cycle, 
                                :flt_initdt, :flt_dockhour,
                                :flt_iamount, :flt_eamount,:FLT_INSERTUSER,:FLT_INSERTDT from ST_FLIGHTS where FLT_SLN_ID =  :flt_sln_id and FLT_SCHDUL= :flt_schdul) t";

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(orsql);
            db.AddInParameter(dbCommand, "flt_sln_id", DbType.Int32, Int32.Parse(flt_sln_id));
           // db.AddInParameter(dbCommand, "flt_no", DbType.String, flt_no);
            db.AddInParameter(dbCommand, "FLT_TER_CST_ID", DbType.String, FLT_TER_CST_ID);
            db.AddInParameter(dbCommand, "flt_schdul", DbType.String, flt_schdul);
            db.AddInParameter(dbCommand, "flt_cycle", DbType.String, flt_cycle);
            db.AddInParameter(dbCommand, "flt_initdt", DbType.Date, DateTime.Parse(flt_initdt));

            ParameterAdd(flt_dockhour,"flt_dockhour", db, dbCommand);
            ParameterAdd(flt_iamount, "flt_iamount", db, dbCommand);
            ParameterAdd(flt_eamount, "flt_eamount", db, dbCommand);
            db.AddInParameter(dbCommand, "FLT_INSERTDT", DbType.Date, DateTime.Now);
            db.AddInParameter(dbCommand, "FLT_INSERTUSER", DbType.String,user);

            return db.ExecuteNonQuery(dbCommand);
        }

        private static void ParameterAdd(string flt_dockhour, string parameter, Database db, DbCommand dbCommand)
        {
            bool check;
            int result = 0;


            //check = Int32.TryParse(flt_dockhour, out reslut);
            check = Int32.TryParse(flt_dockhour, out result);
            if (check)
            {
                db.AddInParameter(dbCommand, parameter, DbType.Int32, result);
            }
            else
            {
                db.AddInParameter(dbCommand, parameter, DbType.Int32, DBNull.Value);
            }
        }
        public static int Delete_St_flights(int flt_id)
        { 
            string orsql = "delete from st_flights where flt_id = :flt_id";
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(orsql);
            db.AddInParameter(dbCommand, "flt_id", DbType.Int32, flt_id);
            return db.ExecuteNonQuery(dbCommand);
        }


        /// <summary>
        /// 根据服务航线获取预分码头
        /// </summary>
        /// <param name="sln_ID">服务航线ID</param>
        /// <returns></returns>
        public static DataTable Get_CST_NameBySLN(string sln_ID, string ageID)
        {
            string sql = @"select 
                      sl.sln_id,cst.cst_id, cst.cst_cstmcd, cst.cst_cstmnm
                       from st_service_lines sl, st_customers cst where sl.SLN_TER_CST_ID =cst.cst_id and sln_id = {0} {1}";
            if (ageID == null)
            {
                sql = string.Format(sql, sln_ID, "");
            }
            else
            {
                sql = string.Format(sql, sln_ID, string.Format(" and ( sl.SLN_CST_VCM_ID ={0}  or sl.SLN_CST_AGT_ID= {0} )", ageID));
            }
            Database db = DatabaseFactory.CreateDatabase();
            return db.ExecuteDataTable(CommandType.Text, sql);
        }

        /// <summary>
        /// 获取班期配置表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetFLT_SCHDUL()
        {
            string sql = "select RV_LOW_VALUE from st_ref_codes t where rv_domain = 'FLT_SCHDUL'";

            Database db = DatabaseFactory.CreateDatabase();
            return db.ExecuteDataTable(CommandType.Text, sql);
        }



        #region new add

        /// <summary>
        /// 获取web_vm_flights的所有信息
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="strOrder"></param>
        /// <returns></returns>
        public DataTable GetTable(string strWhere, string strOrder)
        {
            string sql = "select * from web_vm_flights";
            //  string query = string.Empty;
            if (!string.IsNullOrEmpty(strWhere))
                sql += " where " + strWhere;
            if (!string.IsNullOrEmpty(strOrder))
                sql += " order by " + strOrder;

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(dbCommand);
            if (dt != null)
                dt.TableName = "web_vm_flights";
            return dt;

        }


        /// <summary>
        /// 执行添加、修改、删除操作
        /// </summary>
        /// <param name="operationType"></param>
        /// <param name="userID"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public string InsertUpdateDeleteOperation(int operationType, int userID, WEB_VM_FLIGHTS model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("web_sp_process_flightinfo");
            db.AddInParameter(dbCommand, "P_OperationType", DbType.Decimal, operationType);

            db.AddInParameter(dbCommand, "P_User_ID", DbType.Decimal, userID);

            db.AddInParameter(dbCommand, "P_FLT_SLN_ID", DbType.Decimal, GetData(model.FLT_SLN_ID));

            db.AddInParameter(dbCommand, "P_FLT_SCHDUL", DbType.String, GetData(model.FLT_SCHDUL));
            db.AddInParameter(dbCommand, "P_FLT_CYCLE", DbType.String, GetData(model.FLT_CYCLE));
            db.AddInParameter(dbCommand, "P_FLT_INITDT", DbType.DateTime, GetData(model.FLT_INITDT));

            db.AddInParameter(dbCommand, "P_FLT_IAMOUNT", DbType.Decimal, GetData(model.FLT_IAMOUNT));

            db.AddInParameter(dbCommand, "P_FLT_EAMOUNT", DbType.Decimal, GetData(model.FLT_EAMOUNT));

            db.AddInParameter(dbCommand, "P_FLT_DOCKHOUR", DbType.Decimal, GetData(model.FLT_DOCKHOUR));


            db.AddInParameter(dbCommand, "P_FLT_IEFG", DbType.String, GetData(model.FLT_IEFG));
            db.AddInParameter(dbCommand, "P_FLT_STARTM", DbType.DateTime, GetData(model.FLT_STARTM));
            db.AddInParameter(dbCommand, "P_FLT_ENDDT", DbType.DateTime, GetData(model.FLT_ENDDT));
            db.AddInParameter(dbCommand, "P_FLT_TER_CST_ID", DbType.Decimal, GetData(model.FLT_TER_CST_ID));
            db.AddInParameter(dbCommand, "P_FLT_ID", DbType.Decimal, GetData(model.FLT_ID));

            db.AddOutParameter(dbCommand, "ReturnValue", DbType.String, 100);

            db.ExecuteNonQuery(dbCommand);

            string str = db.GetParameterValue(dbCommand, "ReturnValue").ToString();
            return str;

        }

        private object GetData(object value)
        {
            if (value == null || string.IsNullOrEmpty(value.ToString()))
                return DBNull.Value;
            return value;
        }

        #endregion


    }
}
